package com.web.login.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import com.web.login.connection.DatabaseConnection;
import com.web.login.model.User;

public class CRUDServiceImpl implements CRUDService {

	private static Connection conn = new DatabaseConnection().connectDatabase();

	public void createOrEditUser(Long id, User user) {
		try {
			if (id == (long) -1) {
				Statement stmt = conn.createStatement();
				stmt.execute("INSERT INTO users (name,gender,country,aboutyou,mailinglist) VALUES ('"
						+ user.getName()
						+ "','"
						+ user.getGender()
						+ "','"
						+ user.getCountry()
						+ "','"
						+ user.getAboutYou()
						+ "', " + user.getMailingList() + ");");
			} else {
				Statement stmt = conn.createStatement();
				String sql = "UPDATE users SET name='" + user.getName()
						+ "',gender='" + user.getGender() + "',country='"
						+ user.getCountry() + "',aboutyou='"
						+ user.getAboutYou() + "',mailinglist="
						+ user.getMailingList() + " WHERE id=" + id + ";";
				stmt.execute(sql);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public User editUser(Long id) {
		User aUser = null;
		try {
			Statement stmt = conn.createStatement();
			String sql = "SELECT * FROM users WHERE id=" + id + ";";
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				aUser = new User(rs.getInt("id"), rs.getString("name"),
						rs.getString("gender"), rs.getString("country"),
						rs.getString("aboutyou"), rs.getBoolean("mailinglist"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return aUser;
	}

	public void deleteUser(Long id) {
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("DELETE FROM users WHERE id=" + id);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void readUser(List<User> listUser) {
		try {
			if (listUser.size() > 0) {
				listUser.clear();
			}
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM users ORDER BY id");
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String gender = rs.getString("gender");
				String country = rs.getString("country");
				String aboutYou = rs.getString("aboutyou");
				Boolean mailingList = rs.getBoolean("mailinglist");
				User e = new User(id, name, gender, country, aboutYou,
						mailingList);
				listUser.add(e);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void deleteAllUsers() {

		try {

			Statement stmt = conn.createStatement();
			String sql = "DELETE FROM users;";
			stmt.execute(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
